package Zoo.impl;

import Zoo.entity.LeaderEntity;
import Zoo.entity.ZoomenEntity;
import Zoo.service.LeaderService1;
import Zoo.util.TableDTO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

/**
 * 1、园长所看到的动物饲养员信息界面
 * 2、对动物饲养员增删改查
 */

public class LeaderService1Impl implements LeaderService1 {

    //查询
    @Override
    public TableDTO retrieveLeader1(LeaderEntity leaderEntity) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT * FROM `zoomen` ");
        if (leaderEntity.getSearchKey() != null && !"".equals(leaderEntity.getSearchKey().trim())) {
            sql.append("where `name` like '%" + leaderEntity.getSearchKey().trim() + "%'");
        }

        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        //查出来的数据存在这里
        TableDTO returnDTO = new TableDTO();


        try {
            conn = com.JdbcUtils_DBCP.getConnection();
            st = conn.prepareStatement(sql.toString());
            rs = st.executeQuery();
            //存放连接数据库查出来的数据
            returnDTO.setData(fillData(rs));


            return returnDTO;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            com.JdbcUtils_DBCP.release(conn, st, rs);
        }
        return null;
    }


    //从数据库查出来的数据
    private Vector<Vector<Object>> fillData(ResultSet rs) throws SQLException {
        Vector<Vector<Object>> data = new Vector<>();
        while (rs.next()) {
            Vector<Object> oneRecord = new Vector<>();
            int id = rs.getInt("id");
            String idNumber = rs.getString("id_number");
            String phone = rs.getString("phone");
            String name = rs.getString("name");


            oneRecord.addElement(id);
            oneRecord.addElement(idNumber);
            oneRecord.addElement(phone);
            oneRecord.addElement(name);


            data.addElement(oneRecord);
        }
        return data;
    }

    @Override
    public boolean update(ZoomenEntity zoomenEntity) {
        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE `zoomen` SET `id_number`=?,`phone`=?,`name`=? WHERE `id`=?");

        Connection conn = null;
        PreparedStatement st = null;


        try {
            conn = com.JdbcUtils_DBCP.getConnection();
            st = conn.prepareStatement(sql.toString());
            st.setString(1, zoomenEntity.getIdNumeber());
            st.setString(2, zoomenEntity.getPhone());
            st.setString(3, zoomenEntity.getZoomenName());
            st.setInt(4, zoomenEntity.getId());


            return st.executeUpdate() == 1;


        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            com.JdbcUtils_DBCP.release(conn, st, null);
        }
        return false;
    }

    @Override
    public boolean add(ZoomenEntity zoomenEntity) {
        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO `zoomen` (`id_number`,`phone`,`name`) " +
                " VALUES(?,?,?)");

        Connection conn = null;
        PreparedStatement st = null;


        try {
            conn = com.JdbcUtils_DBCP.getConnection();
            st = conn.prepareStatement(sql.toString());
            st.setString(1, zoomenEntity.getIdNumeber());
            st.setString(2, zoomenEntity.getPhone());
            st.setString(3, zoomenEntity.getZoomenName());

            return st.executeUpdate() == 1;


        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            com.JdbcUtils_DBCP.release(conn, st, null);
        }
        return false;
    }

    @Override
    public boolean delete(ZoomenEntity zoomenEntity) {
        StringBuilder sql = new StringBuilder();
        sql.append("delete from `zoomen`  WHERE `id`=?");

        Connection conn = null;
        PreparedStatement st = null;


        try {
            conn = com.JdbcUtils_DBCP.getConnection();
            st = conn.prepareStatement(sql.toString());

            st.setInt(1, zoomenEntity.getId());


            return st.executeUpdate() == 1;


        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            com.JdbcUtils_DBCP.release(conn, st, null);
        }
        return false;
    }
}
